unit UDB;

interface

uses
  ZConnection, ZDataset, UTypes;

type
  TDB = class
  private
    FZConnection: TZConnection;
    FZROQuery: TZReadOnlyQuery;
    FBidInfo: TBidInfo;
    function GetStartDateTime: TDateTime;
    function GetAvailableDateTime: TDateTime;
  protected
  public
    constructor Create;
    destructor Destroy; override;

    function GetTimeOut: Byte;
    procedure LastBidInfo;
    procedure TerminateAuction;
    function EndedAuction: Boolean;
    function RunningAuction(aTimeOut: Byte; aStartDateTime: TDateTime): Boolean;
    function RunningPreAuction: Boolean;
    function CurrentValue: Currency;

    property BidInfo: TBidInfo read FBidInfo;
    property StartDateTime: TDateTime read GetStartDateTime;
    property AvailableDateTime: TDateTime read GetAvailableDateTime;
  end;

implementation

uses Windows, SysUtils, UConfigurations;

const
  SQL_TIMEOUT = 'SELECT OBTERTIMEOUT(%U)';

  SQL_LASTBID =
  '  SELECT USU.BI_USUARIO_ID'#13#10 +
  '       , LAN.DT_LANCE AS DT_LANCE'#13#10 +
  '       , USU.VA_APELIDO'#13#10 +
  '       , LAN.BI_LANCE_ID'#13#10 +
  '    FROM LANCE LAN'#13#10 +
  '    JOIN USUARIO USU USING(BI_USUARIO_ID)'#13#10 +
  '   WHERE LAN.BI_LEILAO_ID = %U'#13#10 +
  'ORDER BY LAN.BI_LANCE_ID DESC'#13#10 +
  '   LIMIT 1';

  SQL_ENDAUCTION = 'CALL FINALIZARLEILAO(%U)';

  SQL_ENDEDAUCTION = 'SELECT LEILAOFINALIZADO(%U)';

  SQL_CURRENTVALUE = 'SELECT VALORARREMATE(%U)';

  SQL_AVAILABLEDATETIME = 'SELECT DISPONIBILIZACAO(%U)';

  SQL_STARTDATETIME = 'SELECT INICIO(%U)';

{ DB }

constructor TDB.Create;
begin
  FZConnection := TZConnection.Create(nil);

  FZConnection.Database := Configurations.Database;
  FZConnection.User := Configurations.UserName;
  FZConnection.Password := Configurations.Password;
  FZConnection.Port := Configurations.PortNumb;
  FZConnection.Protocol := Configurations.Protocol;
  FZConnection.HostName := Configurations.HostName;
  FZConnection.Connect;

  FZROQuery := TZReadOnlyQuery.Create(FZConnection);
  FZROQuery.Connection := FZConnection;
end;

destructor TDB.Destroy;
begin
  FZROQuery.Close;
  FZROQuery.Free;
  FZConnection.Free;
  inherited;
end;

function TDB.EndedAuction: Boolean;
begin
  with FZROQuery do
    try
      SQL.Text := Format(SQL_ENDEDAUCTION,[Configurations.AuctionID]);
      Open;
      Result := Fields[0].AsInteger = 1;
    finally
      Close;
    end;
end;

function TDB.CurrentValue: Currency;
begin
  with FZROQuery do
    try
      SQL.Text := Format(SQL_CURRENTVALUE,[Configurations.AuctionID]);
      Open;
      Result := Fields[0].AsCurrency;
    finally
      Close;
    end;
end;

procedure TDB.LastBidInfo;
begin
  ZeroMemory(@FBidInfo,SizeOf(TBidInfo));

  with FZROQuery do
    try
      SQL.Text := Format(SQL_LASTBID,[Configurations.AuctionID]);
      Open;

      if RecordCount = 1 then
      begin
        FBidInfo.BI_USER_ID  := Fields[0].AsInteger;
        FBidInfo.DT_BID      := Fields[1].AsDateTime;
        FBidInfo.VA_NICKNAME := Fields[2].AsString;
        FBidInfo.BI_LANCE_ID := Fields[3].AsInteger;
      end;

    finally
      Close;
    end;
end;

function TDB.GetTimeOut: Byte;
begin
  with FZROQuery do
    try
      SQL.Text := Format(SQL_TIMEOUT,[Configurations.AuctionID]);
      Open;
      Result := Fields[0].AsInteger;
    finally
      Close;
    end;
end;

function TDB.RunningAuction(aTimeOut: Byte; aStartDateTime: TDateTime): Boolean;
begin
  LastBidInfo;

  if FBidInfo.DT_BID >= aStartDateTime then
    Result := (FBidInfo.DT_BID + (aTimeOut / SecsPerDay)) > Now
  else
    Result := (aStartDateTime + (aTimeOut / SecsPerDay)) > Now;
end;

function TDB.RunningPreAuction: Boolean;
begin
  LastBidInfo;
  Result := (Now >= GetAvailableDateTime) and (Now < GetStartDateTime);
end;

function TDB.GetAvailableDateTime: TDateTime;
begin
  with FZROQuery do
    try
      SQL.Text := Format(SQL_AVAILABLEDATETIME,[Configurations.AuctionID]);
      Open;
      Result := Fields[0].AsDateTime;
    finally
      Close;
    end;
end;

function TDB.GetStartDateTime: TDateTime;
begin
  with FZROQuery do
    try
      SQL.Text := Format(SQL_STARTDATETIME,[Configurations.AuctionID]);
      Open;
      Result := Fields[0].AsDateTime;
    finally
      Close;
    end;
end;

procedure TDB.TerminateAuction;
begin
  with FZROQuery do
    try
      SQL.Text := Format(SQL_ENDAUCTION,[Configurations.AuctionID]);
      ExecSQL;
    finally
      Close;
    end;
end;

end.
